unit unitEgoDepletion;

{$mode ObjFPC}{$H+}

interface

uses
  Classes, SysUtils, DB, SQLDB, SQLite3Conn, Forms, Controls, Graphics, Dialogs,
  ExtCtrls, StdCtrls, DBGrids, Buttons, ComCtrls, Spin, DateTimePicker, SpinEx,
  TAGraph, TASeries, TADbSource, TARadialSeries, LCLType, unitDMMain,
  TAChartUtils, TALegend;

type

  { TFrmEgoDepletion }

  TFrmEgoDepletion = class(TForm)
    Bevel1: TBevel;
    BtnExport: TBitBtn;
    BtnDatCancel: TBitBtn;
    TrendChart: TChart;
    TrendChartLineSeries1: TLineSeries;
    CostChart: TChart;
    CostChartBarSeries1: TBarSeries;
    GroupBox4: TGroupBox;
    GroupBox5: TGroupBox;
    SubjectChart: TChart;
    SubjectChartPieSeries1: TPieSeries;
    DSQry: TDataSource;
    AM_DBGrid: TDBGrid;
    Bevel2: TBevel;
    BtnDatReset: TBitBtn;
    BtnDatNew: TBitBtn;
    BtnDatModify: TBitBtn;
    BtnDatRemove: TBitBtn;
    BtnDatSave: TBitBtn;
    BtnQuery: TBitBtn;
    DTickerDatStart: TDateTimePicker;
    DTickerDatEnd: TDateTimePicker;
    DTickerQryStart: TDateTimePicker;
    DTPickerQryEnd: TDateTimePicker;
    EdtDataId: TEdit;
    EdtDataSubject: TEdit;
    EdtQrySubject: TEdit;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Label10: TLabel;
    Label11: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    EdtDataDesc: TMemo;
    LabTips: TLabel;
    Panel1: TPanel;
    Panel10: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    Panel8: TPanel;
    Panel9: TPanel;
    SpinEdtDegree: TSpinEdit;
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    Splitter4: TSplitter;
    Splitter5: TSplitter;
    SQLQueryQry: TSQLQuery;
    Splitter3: TSplitter;
    procedure AM_DBGridDblClick(Sender: TObject);
    procedure BtnDatCancelClick(Sender: TObject);
    procedure BtnDatRemoveClick(Sender: TObject);
    procedure BtnDatResetClick(Sender: TObject);
    procedure BtnDatModifyClick(Sender: TObject);
    procedure BtnDatNewClick(Sender: TObject);
    procedure BtnDatSaveClick(Sender: TObject);
    procedure BtnExportClick(Sender: TObject);
    procedure BtnQueryClick(Sender: TObject);
    procedure DoneSubjectChart();
    procedure DoneCostChart();
    procedure DoneTrendChart();
    procedure FormCreate(Sender: TObject);
    procedure ClearInput();
    procedure QueryList();
    procedure MakeQueryWhereString();
    procedure EnableInput(const status: boolean);
  private

  public

  end;
  procedure InsertIntoSQLiteTable(const Subject, Description, Start, Over, Degree, Notes: String);
  procedure ModifySQLiteTable(const Id, Subject,Description,Start,Over,Degree,Notes: String);
  procedure RemoveSQLiteTable(const id: String);
  procedure ExportToCSV(SQLQuery: TSQLQuery; const FileName: string);

var
  FrmEgoDepletion: TFrmEgoDepletion;
  optStatus: String;
  FormatSettings: TFormatSettings;
  QueryWhereString: String;

implementation

{$R *.lfm}

{ TFrmEgoDepletion }

procedure TFrmEgoDepletion.AM_DBGridDblClick(Sender: TObject);
var
  Grid: TDBGrid;
  DataSet: TDataSet;
  Start,Over: String;
  dtStart,dtOver: TDateTime;
begin
  Grid := Sender as TDBGrid;
  DataSet := Grid.DataSource.DataSet;
  if(DataSet.RecordCount>0) and (optStatus='') then
  begin
    EdtDataId.Text:=DataSet.FieldByName('id').AsString;
    EdtDataSubject.Text:=DataSet.FieldByName('subject_matter').AsString;
    EdtDataDesc.Text:=DataSet.FieldByName('description').AsString;
    SpinEdtDegree.Text:=DataSet.FieldByName('hazard_degree').AsString;

    Start := DataSet.FieldByName('start_time').AsString;
    if (TryStrToDateTime(Start,dtStart,FormatSettings))then DTickerDatStart.DateTime:=dtStart;

    Over := DataSet.FieldByName('end_time').AsString;
    if (TryStrToDateTime(Over,dtOver,FormatSettings))then DTickerDatEnd.DateTime:=dtOver;

    BtnDatNew.Enabled:=false;
    BtnDatModify.Enabled:=true;
  end;
end;

procedure TFrmEgoDepletion.BtnDatCancelClick(Sender: TObject);
begin
  optStatus:='';
  BtnDatNew.Enabled:=true;
  BtnDatModify.Enabled:=false;
  EnableInput(false);
end;

procedure TFrmEgoDepletion.BtnDatRemoveClick(Sender: TObject);
var
  Id,tips: String;
begin
  if(optStatus='') and (length(EdtDataId.Text)>0)then
  begin
    if( MessageDlg('您确定删除当前记录？', mtConfirmation, [mbNo, mbYes], 0)=mrNo) then exit;

    Id:=EdtDataId.Text;
    try
      try
        RemoveSQLiteTable(Id);
        tips := '提示：成功删除记录';
        ClearInput();
        QueryList();
      except
        on E: Exception do begin
          showmessage(E.Message);
          tips := '提示：删除记录失败';
        end;
      end;
    finally
      LabTips.Caption:=tips;
      optStatus:='';
      BtnDatNew.Enabled:=true;
      BtnDatModify.Enabled:=false;
    end;

  end
  else Application.MessageBox('请从记录列表中，双击选定所要删除的记录','警告',MB_ICONWARNING);
end;

procedure TFrmEgoDepletion.BtnDatResetClick(Sender: TObject);
begin
  ClearInput();
end;

procedure TFrmEgoDepletion.BtnDatModifyClick(Sender: TObject);
begin
  optStatus := 'Modify';
  BtnDatNew.Enabled:=false;
  EnableInput(true);
  EdtDataSubject.SetFocus;
end;

procedure TFrmEgoDepletion.BtnDatNewClick(Sender: TObject);
begin
  optStatus := 'New';
  ClearInput();
  EnableInput(true);
  EdtDataSubject.SetFocus;
end;

procedure TFrmEgoDepletion.BtnDatSaveClick(Sender: TObject);
var
  Subject, Description, Start, Over, Degree, Notes, Id:String;
  tips: String;
begin
  if(optStatus='')then
  begin
    Application.MessageBox('请确定您的操作，新增或修改？','提示',MB_OK);
    exit;
  end;

  // ''2025-01-17 20'#$E6'32'#$E6'57'' LabTips
  Start := FormatDateTime('yyyy-mm-dd ', DTickerDatStart.DateTime)
          + FormatDateTime('hh', DTickerDatStart.DateTime) + ':'
          + FormatDateTime('nn', DTickerDatStart.DateTime) + ':'
          + FormatDateTime('ss', DTickerDatStart.DateTime);

  Over := FormatDateTime('yyyy-mm-dd ', DTickerDatEnd.DateTime)
          + FormatDateTime('hh', DTickerDatEnd.DateTime) + ':'
          + FormatDateTime('nn', DTickerDatEnd.DateTime) + ':'
          + FormatDateTime('ss', DTickerDatEnd.DateTime);

  Subject:=EdtDataSubject.Text;
  Description:=EdtDataDesc.Text;
  Degree:=IntToStr(SpinEdtDegree.VALUE);
  Notes:='';

  if(trim(Subject)='') or (trim(Description)='') or (trim(Degree)='') or (trim(Start)='') or (trim(Over)='') then
  begin
    Application.MessageBox('事项、内容描述、严重程度、开始时间、持续时间'#13#10'等栏位的内容不能为空','警告',MB_ICONWARNING);
    exit;
  end;

  if(optStatus='New')then
  begin
    try
      InsertIntoSQLiteTable(Subject, Description, Start, Over, Degree, Notes);
      tips := '提示：成功新增记录';
    except
      on E: Exception do begin
        showmessage(E.Message);
        tips := '提示：新增记录失败';
      end;
    end;
  end;

  if(optStatus='Modify')then
  begin
    Id := EdtDataId.Text;
    try
      ModifySQLiteTable(Id, Subject, Description, Start, Over, Degree, Notes);
      tips := '提示：成功修改记录';
    except
      on E: Exception do begin
        showmessage(E.Message);
        tips := '提示：修改记录失败';
      end;
    end;
  end;

  // 清除状态
  optStatus:='';
  BtnDatNew.Enabled:=true;
  BtnDatModify.Enabled:=false;
  LabTips.Caption:=tips;
  EnableInput(false);

  // 刷新数据
  ClearInput();
  QueryList();
  DoneSubjectChart();
  DoneCostChart();
  DoneTrendChart();
end;

procedure TFrmEgoDepletion.BtnExportClick(Sender: TObject);
begin
  if SQLQueryQry.RecordCount > 0 then
  begin
    ExportToCSV(SQLQueryQry, ExtractFilePath(ParamStr(0)) + FormatDateTime('yyyyMMdd_hhnnss', now) +'.csv');
  end else showmessage('请查询再导出 CSV 文件');
end;

procedure TFrmEgoDepletion.BtnQueryClick(Sender: TObject);
begin
  QueryList();
  DoneSubjectChart();
  DoneCostChart();
  DoneTrendChart();
end;

procedure TFrmEgoDepletion.DoneSubjectChart();
var
  SQLQuery: TSQLQuery;
  Subject: String;
  Count: Integer;
begin

  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'SELECT subject_matter,COUNT(subject_matter) AS cunt FROM ego_depletion '
                    +  QueryWhereString
                    + ' GROUP BY subject_matter ';

  SubjectChartPieSeries1.Clear;

  try
    try
      SQLQuery.Close;
      SQLQuery.Open;
      while not SQLQuery.Eof do
      begin
        if(not SQLQuery.FieldByName('subject_matter').IsNull) and (not SQLQuery.FieldByName('cunt').IsNull) then
        begin
          Subject:=SQLQuery.FieldByName('subject_matter').AsString;
          Count:=SQLQuery.FieldByName('cunt').AsInteger;
          SubjectChartPieSeries1.Add(Count, Subject);
        end;
        SQLQuery.Next;
      end;
      SQLQuery.Close;
    except
      on E: Exception do
      begin
        ShowMessage('“内耗事项占比”统计项，发生异常：' + E.Message);
      end;
    end;
  finally
    SQLQuery.Free;
  end;

end;

procedure TFrmEgoDepletion.DoneCostChart();
var
  SQLQuery: TSQLQuery;
  Subject: String;
  Count: Double;
begin

  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'SELECT subject_matter, Round(SUM(CAST(((strftime(''%s'', end_time)-strftime(''%s'', start_time))/3600.0) AS DECIMAL(10,4))),2) as diff_hours FROM ego_depletion '
                    +  QueryWhereString
                    + ' GROUP BY subject_matter ';

  CostChartBarSeries1.Clear;

  try
    try
      SQLQuery.Close;
      SQLQuery.Open;
      while not SQLQuery.Eof do
      begin
        if(not SQLQuery.FieldByName('subject_matter').IsNull) and (not SQLQuery.FieldByName('diff_hours').IsNull) then
        begin
          Subject:=SQLQuery.FieldByName('subject_matter').AsString;
          Count:=SQLQuery.FieldByName('diff_hours').AsFloat;
          CostChartBarSeries1.Add(Count, Subject);
        end;
        SQLQuery.Next;
      end;
      SQLQuery.Close;
    except
      on E: Exception do
      begin
        ShowMessage('“内耗事项耗费时间合计（小时）”统计项，发生异常：' + E.Message);
      end;
    end;
  finally
    SQLQuery.Free;
  end;

end;

procedure TFrmEgoDepletion.DoneTrendChart();
var
  SQLQuery: TSQLQuery;
  Subject: String;
  Count: Integer;
begin

  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := ' SELECT subStr(start_time,1,10) as ymd, count(subject_matter) AS cunt FROM ego_depletion '
                    +  QueryWhereString
                    + ' GROUP BY subStr(start_time,1,10) ORDER BY subStr(start_time,1,10) ';

  TrendChartLineSeries1.Clear;

  try
    try
      SQLQuery.Close;
      SQLQuery.Open;
      while not SQLQuery.Eof do
      begin
        if(not SQLQuery.FieldByName('ymd').IsNull) and (not SQLQuery.FieldByName('cunt').IsNull) then
        begin
          Subject:=SQLQuery.FieldByName('ymd').AsString;
          Count:=SQLQuery.FieldByName('cunt').AsInteger;
          TrendChartLineSeries1.Add(Count, Subject);
        end;
        SQLQuery.Next;
      end;
      SQLQuery.Close;
    except
      on E: Exception do
      begin
        ShowMessage('“内耗日均趋势图（出现次数）”统计项，发生异常：' + E.Message);
      end;
    end;
  finally
    SQLQuery.Free;
  end;

end;


procedure TFrmEgoDepletion.FormCreate(Sender: TObject);
var
    today: TDateTime;
begin
  // 1.
  //DatabaseNameConnString :=  GetCurrentDir() + '/data/absent_minded.dat';
  today := now();
  DTickerQryStart.DateTime := today-3;
  DTPickerQryEnd.DateTime := today;
  DTickerDatStart.DateTime := today - (15 / (24 * 60));
  DTickerDatEnd.DateTime := today;

  BtnDatModify.Enabled:=false;
  EnableInput(false);

  QueryWhereString:='';

  // 2.
  // 初始化日期和时间格式设置
  // 设置日期分隔符和时间分隔符
  FormatSettings.LongDateFormat := 'yyyy-MM-dd';
  FormatSettings.ShortDateFormat := 'yyyy-MM-dd';
  FormatSettings.LongTimeFormat := 'hh:mm:ss';
  FormatSettings.ShortTimeFormat := 'hh:mm:ss';
  FormatSettings.DateSeparator := '-';
  FormatSettings.timeSeparator := ':';

  try
    // 3.创建SQL查询
    SQLQueryQry.DataBase:=DMMain.UMConn;
    SQLQueryQry.Transaction:=DMMain.UMransaction;
  except
    on E: Exception do
    begin
      ShowMessage('请退出当前软件，发生异常：' + E.Message);
    end;
  end;
end;

procedure InsertIntoSQLiteTable(const Subject,Description,Start,Over,Degree,Notes: String);
var
  SQLQuery: TSQLQuery;
begin
  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'INSERT INTO ego_depletion(subject_matter,description,start_time,end_time,hazard_degree,notes) '
                    + 'VALUES(:Subject,:Description,:Start,:Over,:Degree,:Notes)';

  // 3. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ParamByName('Subject').AsString := Subject;
      SQLQuery.ParamByName('Description').AsString := Description;
      SQLQuery.ParamByName('Start').AsString := Start;
      SQLQuery.ParamByName('Over').AsString := Over;
      SQLQuery.ParamByName('Degree').AsString := Degree;
      SQLQuery.ParamByName('Notes').AsString := Notes;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;

procedure ModifySQLiteTable(const Id, Subject,Description,Start,Over,Degree,Notes: String);
var
  SQLQuery: TSQLQuery;
begin
  // 4. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 5. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := ' UPDATE ego_depletion SET subject_matter=:Subject,description=:Description,'
                    +  ' start_time=:Start,end_time=:Over,hazard_degree=:Degree,notes=:Notes '
                    +  ' WHERE (id=:id) ';

  // 6. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ParamByName('Subject').AsString := Subject;
      SQLQuery.ParamByName('Description').AsString := Description;
      SQLQuery.ParamByName('Start').AsString := Start;
      SQLQuery.ParamByName('Over').AsString := Over;
      SQLQuery.ParamByName('Degree').AsString := Degree;
      SQLQuery.ParamByName('Notes').AsString := Notes;
      SQLQuery.ParamByName('id').AsString := Id;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;

procedure RemoveSQLiteTable(const id: String);
var
  SQLQuery: TSQLQuery;
begin
  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'DELETE FROM ego_depletion WHERE id=' + QuotedStr(id);

  // 3. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;

procedure TFrmEgoDepletion.ClearInput();
var
    today: TDateTime;
begin
  today:=now();
  DTickerDatStart.DateTime := today - (15 / (24 * 60));
  DTickerDatEnd.DateTime := today;
  EdtDataId.Text:='';
  EdtDataSubject.Text:='';
  EdtDataDesc.Text:='';
  SpinEdtDegree.Text:='6';
end;

procedure TFrmEgoDepletion.EnableInput(const status: boolean);
begin
  DTickerDatStart.Enabled := status;
  DTickerDatEnd.Enabled := status;
  EdtDataSubject.Enabled := status;
  EdtDataDesc.Enabled := status;
  SpinEdtDegree.Enabled := status;
end;

procedure TFrmEgoDepletion.MakeQueryWhereString();
var
  Keyword, Start, Over: String;
  arr: TStringList;
  i: Integer;
begin
  arr := TStringList.Create;
  arr.Delimiter := ' ';
  arr.DelimitedText := EdtQrySubject.Text;

  Keyword:='';
  for i := 0 to arr.Count-1 do
    if((i+1)=arr.Count)then
      Keyword:= Keyword + ' ((subject_matter LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (description LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (notes LIKE ' + QuotedStr('%' + arr[i] +'%') + '))'
    else
      Keyword:= Keyword + ' ((subject_matter LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (description LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (notes LIKE ' + QuotedStr('%' + arr[i] +'%') + ')) OR ';

  Start:=FormatDateTime('yyyy-mm-dd ', DTickerQryStart.Date);
  Over:=FormatDateTime('yyyy-mm-dd ', DTPickerQryEnd.Date+1);

  QueryWhereString:= ' WHERE (1=1) '
                  + ' AND (start_time >= ' + QuotedStr(Start) + ') '
                  + ' AND (start_time < ' + QuotedStr(Over) + ') ';

  if(length(Keyword)>0) then QueryWhereString:=QueryWhereString+' AND ('+Keyword+') ';

end;


procedure TFrmEgoDepletion.QueryList();
begin

  MakeQueryWhereString();

  SQLQueryQry.SQL.Text:='SELECT id,subject_matter,description,start_time,end_time,hazard_degree,notes FROM ego_depletion '
                      +QueryWhereString
                      +' ORDER BY start_time ASC ';

  //showmessage(SQLQueryQry.SQL.Text);

  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox1.Caption:='记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox1.Caption:='记录列表(共0条)' ;
end;

procedure ExportToCSV(SQLQuery: TSQLQuery; const FileName: string);
var
  i: Integer;
  CSVFile: TextFile;
  ItemStr: String;
begin
  ItemStr:='';
  try
    try
      AssignFile(CSVFile, FileName);
      Rewrite(CSVFile);

      // 写入列标题
      for i := 0 to SQLQuery.FieldCount - 1 do
      begin
        if((i+1)=SQLQuery.FieldCount) then
          ItemStr := ItemStr + (SQLQuery.Fields[i].FieldName)
        else
          ItemStr := ItemStr + (SQLQuery.Fields[i].FieldName) + #9;
      end;
      WriteLn(CSVFile, ItemStr);

      // 遍历查询结果
      SQLQuery.First;
      while not SQLQuery.Eof do
      begin
        ItemStr:='';

        // 写入数据行
        for i := 0 to SQLQuery.FieldCount - 1 do
        begin
          if ((i+1)=SQLQuery.FieldCount)then
            ItemStr:=ItemStr+SQLQuery.Fields[i].AsString
          else
            ItemStr:=ItemStr+SQLQuery.Fields[i].AsString + #9;
        end;
        WriteLn(CSVFile, ItemStr);

        SQLQuery.Next;
      end;
      ShowMessage('数据成功导出，请查看 CSV 文件：' + FileName);
    except
      on E: Exception do
      begin
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    CloseFile(CSVFile);
  end;
end;
end.

